package easytasks.data;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DataSetup extends SQLiteOpenHelper
{
	private static final String DB_NAME = "tasks.db";
	private static final int DB_VERSION = 1;
	
	public DataSetup(Context context)
	{
		super(context, DB_NAME, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db)
	{
		String initialSql = "PRAGMA foreign_keys = ON;";
		
		String taskCreateSql = "CREATE TABLE tasks ("
			+ "id TEXT PRIMARY KEY NOT NULL, "
			+ "title TEXT NOT NULL, "
			+ "time INTEGER NOT NULL, "
			+ "ignore_hour INTEGER NOT NULL, "
			+ "description TEXT, "
			+ "done INTEGER NOT NULL"
			+ ");";
		
		String taskCatCreateSql = "CREATE TABLE task_categories ("
			+ "id TEXT PRIMARY KEY NOT NULL, "
			+ "name TEXT UNIQUE NOT NULL"
			+ ");";
		
		String taskCatRelCreateSql = "CREATE TABLE task_category_relations ("
			+ "task_id TEXT NOT NULL, "
			+ "category_id TEXT NOT NULL, "
			+ "PRIMARY KEY (task_id, category_id), "
			+ "FOREIGN KEY (task_id) REFERENCES tasks (id) ON UPDATE CASCADE ON DELETE CASCADE, "
			+ "FOREIGN KEY (category_id) REFERENCES task_categories (id) ON UPDATE CASCADE ON DELETE CASCADE"
			+ ");";
		
		db.execSQL(initialSql);
		db.execSQL(taskCreateSql);
		db.execSQL(taskCatCreateSql);
		db.execSQL(taskCatRelCreateSql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		String taskCatRelDropSql = "drop table if exists task_category_relations;";
		String taskCatDropSql = "drop table if exists task_categories;";
		String taskDropSql = "drop table if exists tasks;";
		
		db.execSQL(taskCatRelDropSql);
		db.execSQL(taskCatDropSql);
		db.execSQL(taskDropSql);
		
		onCreate(db);
	}
}
